package com.aimee.dao;

import com.aimee.entity.LeaveWordAnswers;
import com.aimee.entity.LeaveWords;
import com.aimee.entity.Lunbo;
import com.aimee.entity.User;
import com.aimee.util.Dbutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class LeaveWordDAO {

    //查出用户角色
    public List<User> findrole(int user_id) throws SQLException {
        String sql = "SELECT role_id FROM user_role WHERE user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<User> list = runner.query(conn, sql, new BeanListHandler<User>(User.class), user_id);
        conn.close();
        return list;
    }

    //查出所有留言
    public List<LeaveWords> findAllLeaveWords() throws SQLException {
        String sql = "SELECT * FROM leaveword";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<LeaveWords> list = runner.query(conn, sql, new BeanListHandler<LeaveWords>(LeaveWords.class));

        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).leaveWord_id);
        }
        conn.close();
        return list;
    }

    //通过留言id查出留言所对应的用户名
    public List<LeaveWords> findUserNameByuserid(int user_id) throws SQLException {
        String sql = "SELECT user_id,user_name FROM `user` WHERE user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<LeaveWords> list = runner.query(conn, sql, new BeanListHandler<LeaveWords>(LeaveWords.class), user_id);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).user_id + "---" + list.get(i).user_name);
        }
        conn.close();
        return list;
    }

    //通过留言id查询留言回复
    public List<LeaveWordAnswers> findLeaveWordAnswer(int leaveWord_id) throws SQLException {
        String sql = "SELECT * FROM `leaveword-answer` WHERE leaveWord_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<LeaveWordAnswers> list = runner.query(conn, sql, new BeanListHandler<LeaveWordAnswers>(LeaveWordAnswers.class), leaveWord_id);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).answer_id + "--" + list.get(i).leaveWord_id + "--" + list.get(i).answer_content + "--" + list.get(i).answer_date);
        }
        conn.close();
        return list;
    }

    //通过留言回复id查出回复所对应的用户名
    public List<LeaveWordAnswers> findUserNameByansweruserid(int user_id) throws SQLException {
        String sql = "SELECT user_id,user_name FROM `user` WHERE user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<LeaveWordAnswers> list = runner.query(conn, sql, new BeanListHandler<LeaveWordAnswers>(LeaveWordAnswers.class), user_id);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).user_id + "---" + list.get(i).user_name);
        }
        conn.close();
        return list;
    }


    //添加留言
    public int addleaveword(LeaveWords leaveWords) throws SQLException {
        String sql = "INSERT INTO `leaveword` VALUE(NULL,?,?,NOW())";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = 0;
        Object param[] = new Object[2];
        param[0] = leaveWords.getUser_id();
        param[1] = leaveWords.getLeaveWord_content();
        n = runner.update(conn, sql, param);
        conn.close();
        return n;
    }

    //添加回复
    public int addleavewordanswer(LeaveWordAnswers leaveWordAnswers) throws SQLException {
        String sql1 = "INSERT INTO `leaveword-answer` VALUE(NULL,?,?,?,NOW())";
//        String sql2="INSERT INTO `leavanswer_user` VALUE(?,?)";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = 0;
        Object param[] = new Object[3];
        param[0] = leaveWordAnswers.getUser_id();
        param[1] = leaveWordAnswers.getLeaveWord_id();
        param[2] = leaveWordAnswers.getAnswer_content();
        n = runner.update(conn, sql1, param);
        conn.close();
        return n;
    }

    //删除留言以及留言回复
    public void delleaveword(int leaveWord_id) throws SQLException {
        String sql = "delete from leaveword where leaveWord_id=?";
        String sql2 = "delete from `leaveword-answer` where leaveWord_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n1 = runner.update(conn, sql, leaveWord_id);
        int n2 = runner.update(conn, sql2, leaveWord_id);
        System.out.println(n1 + "--" + n2);
        conn.close();
    }

    //删除留言回复
    public void delleavewordanswer(int answer_id) throws SQLException {
        String sql = "delete from `leaveword-answer` where answer_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = runner.update(conn, sql, answer_id);
        System.out.println(n);
        conn.close();
    }


    //    ***************************************我的留言********************************************
//    查找我的留言
    public List<LeaveWords> findmyleave(int user_id) throws SQLException {
        String sql = "SELECT * FROM leaveword WHERE user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<LeaveWords> list = runner.query(conn, sql, new BeanListHandler<LeaveWords>(LeaveWords.class), user_id);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).leaveWord_id);
        }
        conn.close();
        return list;
    }

    //查找我的留言数
    public long countmyleave(int user_id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM `leaveword` WHERE user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), user_id);
        System.out.println(count);
        conn.close();
        return count;
    }

    //删除我的留言通过留言id
    public int delmyleave(int leaveWord_id) throws Exception {
        String sql = "delete from leaveword where leaveWord_id=?";
        String sql2 = "DELETE FROM `leaveword-answer` WHERE leaveWord_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n1 = runner.update(conn, sql, leaveWord_id);
        int n2 = runner.update(conn, sql2, leaveWord_id);
        conn.close();
//        System.out.println(n);
        int n = n1 + n2;
        return n;
    }

    //查找我的回复
    public List<LeaveWordAnswers> findmyanswer(int user_id) throws Exception {
        String sql = "SELECT * FROM `leaveword-answer`WHERE user_id=?";
        String sql2 = "SELECT user_name FROM `user` WHERE user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<LeaveWordAnswers> list = runner.query(conn, sql, new BeanListHandler<LeaveWordAnswers>(LeaveWordAnswers.class), user_id);
        List<LeaveWordAnswers> list2 = runner.query(conn, sql2, new BeanListHandler<LeaveWordAnswers>(LeaveWordAnswers.class), user_id);
        for (int i = 0; i < list.size(); i++) {
            list.get(i).setUser_name(list2.get(0).user_name);
            System.out.println(list.get(i).leaveWord_id);
        }
        conn.close();
        return list;
    }

    //查找我的回复数
    public long countmyanswer(int user_id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM `leaveword-answer` WHERE user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), user_id);
        System.out.println(count);
        conn.close();
        return count;
    }

    //删除我的回复 通过回复id
    public int delmyanswer(int answer_id) throws Exception {
        String sql = "DELETE FROM `leaveword-answer` WHERE answer_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = runner.update(conn, sql, answer_id);
        conn.close();
        System.out.println(n);
        return n;
    }

    //查询对我的回复
    public List<LeaveWordAnswers> findtomeanswer(int user_id) throws Exception {
        String sql = "SELECT * FROM `leaveword-answer` WHERE leaveWord_id IN(SELECT leaveWord_id FROM leaveword WHERE user_id=?)";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<LeaveWordAnswers> list = runner.query(conn, sql, new BeanListHandler<LeaveWordAnswers>(LeaveWordAnswers.class), user_id);
        for (int i = 0; i < list.size(); i++) {
            System.out.println(list.get(i).leaveWord_id);
        }
        conn.close();
        return list;
    }

    //查找对我的回复数
    public long counttomeanswer(int user_id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM `leaveword-answer` WHERE leaveWord_id IN(SELECT leaveWord_id FROM leaveword WHERE user_id=?)";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), user_id);
        System.out.println(count);
        conn.close();
        return count;
    }
}
